/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Departamento;
/**
 * @author Administrador
 */
public class AdminDepartamento implements DepartamentoManager{
    private Connection c;

    public AdminDepartamento(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdDepto() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDDEPARTAMENTO)+1,1) AS ID FROM DEPARTAMENTO";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminDepartamento -> getIdDepto() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void saveDepto(Departamento d)throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO DEPARTAMENTO VALUES("+d.getIdDepartamento();
            query += ", '"+d.getNombre().toUpperCase();
            query += "', '"+d.getSigla().toUpperCase();
            query += "', "+d.isActivo()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminDepartamento -> saveDepto(Departamento d) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Departamento> getDeptos() throws DataException{
        List<Departamento> deptos = new ArrayList<Departamento>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDDEPARTAMENTO";
            query += ", NOMBRE, SIGLA";
            query += " FROM DEPARTAMENTO ";
            query += " WHERE ACTIVO=1";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Departamento d = new Departamento();
                d.setIdDepartamento(rs.getInt("IDDEPARTAMENTO"));
                d.setNombre(rs.getString("NOMBRE"));
                d.setSigla(rs.getString("SIGLA"));
                d.setActivo(true);
                deptos.add(d);
            }
        }catch(SQLException e){
            throw new DataException(" AdminDepartamento -> getDeptos() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return deptos;
    }
    
    @Override
    public List<Departamento> getDeptosInactivos() throws DataException{
        List<Departamento> deptos = new ArrayList<Departamento>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDDEPARTAMENTO";
            query += ", NOMBRE, SIGLA";
            query += " FROM DEPARTAMENTO ";
            query += " WHERE ACTIVO=0";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Departamento d = new Departamento();
                d.setIdDepartamento(rs.getInt("IDDEPARTAMENTO"));
                d.setNombre(rs.getString("NOMBRE"));
                d.setSigla(rs.getString("SIGLA"));
                deptos.add(d);
            }
        }catch(SQLException e){
            throw new DataException(" AdminDepartamento -> getDeptosInactivos() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return deptos;
    }
    
    @Override
    public Departamento getDepartamento(int idDepartamento) throws DataException{
        Departamento d = new Departamento();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDDEPARTAMENTO";
            query += ", NOMBRE, SIGLA, ACTIVO";
            query += " FROM DEPARTAMENTO ";
            query += " WHERE IDDEPARTAMENTO="+idDepartamento;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                d.setIdDepartamento(rs.getInt("IDDEPARTAMENTO"));
                d.setNombre(rs.getString("NOMBRE"));
                d.setSigla(rs.getString("SIGLA"));
                d.setActivo(rs.getBoolean("ACTIVO"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminDepartamento -> getDepartamento(int idDepartamento) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return d;
    }
    
    @Override
    public void updateDepto(Departamento d) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE DEPARTAMENTO SET NOMBRE = '"+d.getNombre().toUpperCase();
            query+= "', SIGLA = '"+d.getSigla().toUpperCase();
            query+= "' WHERE IDDEPARTAMENTO="+d.getIdDepartamento();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminDepartamento -> updateDepto(Departamento d) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void deleteDepto(int idDepartamento) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "DELETE FROM DEPARTAMENTO WHERE IDDEPARTAMENTO="+idDepartamento;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminDepartamento -> deleteDepto(int idDepto) e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseDeptoForDelete(int idDepartamento) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDFACULTAD";
        query += " FROM FACULTAD";
        query += " WHERE IDDEPARTAMENTO="+idDepartamento;
        query += " LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminDepartamento -> isUseDeptoForDelete(int idDepartamento) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void darBaja(int idDepartamento) throws DataException{
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "UPDATE DEPARTAMENTO SET ACTIVO = 0 WHERE IDDEPARTAMENTO="+idDepartamento;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminDepartamento -> darBaja(int idDepartamento) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darAlta(int idDepartamento) throws DataException{
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "UPDATE DEPARTAMENTO SET ACTIVO = 1 WHERE IDDEPARTAMENTO="+idDepartamento;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminDepartamento -> darAlta(int idDepartamento) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
}
